﻿#region
//Copyright (c) 2016-2022 北京网非科技有限公司(wfdot.com)
//
//Permission is hereby granted, free of charge, to any person obtaining a copy
//of this software and associated documentation files (the "Software"), to deal
//in the Software without restriction, including without limitation the rights
//to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
//copies of the Software, and to permit persons to whom the Software is
//furnished to do so, subject to the following conditions:
//
//The above copyright notice and this permission notice shall be included in
//all copies or substantial portions of the Software.
//
//THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
//IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
//FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
//AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
//LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
//THE SOFTWARE.
#endregion
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SumiaAccountBook.DataAccess
{
    public static class DaCommonADO
    {
        private static string ConnectString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectString"].ToString();
                
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="key"></param>
        /// <param name="name"></param>
        public static void InsertStaff(int key, string name)
        {
            using (DbConnection conn = DbProviderFactories.GetFactory("System.Data.SQLite").CreateConnection())
            {
                var path = AppDomain.CurrentDomain.BaseDirectory;
                conn.ConnectionString = "DataSource = " + path + ConnectString + ";Version = 3";
                using (SQLiteCommand cmd = (SQLiteCommand)conn.CreateCommand())
                {
                    cmd.CommandText = @"INSERT INTO Staffs(Key,Name,Memo) VALUES 
                       (@key,@name, '')";
                    conn.Open();
                    cmd.Parameters.AddWithValue("@key", key);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.ExecuteNonQuery();

                }
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="key"></param>
        /// <param name="name"></param>
        public static void InsertCashFlow(Entity.CashFlow entity)
        {
            using (DbConnection conn = DbProviderFactories.GetFactory("System.Data.SQLite").CreateConnection())
            {
                var path = AppDomain.CurrentDomain.BaseDirectory;
                conn.ConnectionString = "DataSource = " + path + ConnectString + ";Version = 3";
                using (SQLiteCommand cmd = (SQLiteCommand)conn.CreateCommand())
                {
                    cmd.CommandText = @"INSERT INTO CashFlows(Type,Category,Amount,Date,ProjectId,StaffId,AccountBookId,Memo) VALUES 
                       (@Type,@Category,@Amount,@Date,@ProjectId,@StaffId,@AccountBookId,@Memo)";
                    conn.Open();
                    cmd.Parameters.AddWithValue("@Type", entity.Type);
                    cmd.Parameters.AddWithValue("@Category", entity.Category);
                    cmd.Parameters.AddWithValue("@Amount", entity.Amount);
                    cmd.Parameters.AddWithValue("@Date", entity.Date);                   
                    cmd.ExecuteNonQuery();

                }
            }
        }
        

        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <param name="list"></param>
        public static void InsertBatch(List<Entity.CashFlow> list)
        {
            if (null == list || list.Count <= 0)
                return;
            using (DbConnection conn = DbProviderFactories.GetFactory("System.Data.SQLite").CreateConnection())
            {
                var path = AppDomain.CurrentDomain.BaseDirectory;
                conn.ConnectionString = "DataSource = " + path + ConnectString + ";Version = 3";
                using (SQLiteCommand cmd = (SQLiteCommand)conn.CreateCommand())
                {
                    cmd.CommandText = @"INSERT INTO CashFlows(Type, Category, Amount, Date, ProjectId, StaffId,AccountBookId,Memo) VALUES 
                       (@Type, @Category, @Amount, @Date, @ProjectId, @StaffId,@AccountBookId,@Memo)";
                    conn.Open();
                    var transaction = conn.BeginTransaction();
                    foreach (var d in list)
                    {
                        cmd.Parameters.AddWithValue("@Type", d.Type);
                        cmd.Parameters.AddWithValue("@Category", d.Category);
                        cmd.Parameters.AddWithValue("@Amount", d.Amount);
                        cmd.Parameters.AddWithValue("@Date", d.Date);
                        cmd.Parameters.AddWithValue("@ProjectId", d.ProjectId);
                        cmd.Parameters.AddWithValue("@StaffId", d.StaffId);
                        cmd.Parameters.AddWithValue("@AccountBookId", d.AccountBookId);
                        cmd.Parameters.AddWithValue("@Memo", d.Memo);                        
                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
        }
    }
}
